package com.mmy.dao.bidtender;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.bidtender.TBidInvite;

public interface TBidInviteRepository extends JpaRepository<TBidInvite, Integer> {
	/**
	 * 个人中心撤标通过招标ID删除招标信息 单量
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tBidInvite where iInviteID =?1", nativeQuery = true)
	void deleteByInviteID(int iInviteID);

	/**
	 * 获得最大编号
	 * 
	 * @return 返回值说明 最大ID
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.11.19
	 */
	@Query(value = "select MAX(iInviteID)  from tBidInvite WHERE LEFT(iInviteID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxInviteID();

	/**
	 * 创建招标信息
	 * 
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.11.19
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tBidInvite(iInviteID, cInviteName, iType, cSynopsis,iZoneID, cContent, dOutDate, dBackDate, cMeal, cStay, cCar, cTrip, cShop, cRecreation, fBudget, dBeginDate, dEndDate,iState,fAmount, cName, cTel, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (?1, NULL, 0, NULL,110101, NULL,'2020-01-01 00:00:00', '2020-01-01 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, 0.00, '2020-01-01 00:00:00', '2020-01-01 00:00:00', 0, 0, NULL, NULL, '2020-01-01 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(int inviteID);

	/**
	 * 查询招标信息 在修改招标时用到
	 * 
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.11.19
	 */
	@Query(value = "select * from tBidInvite where iInviteID=?1", nativeQuery = true)
	TBidInvite findByInviteID(int inviteID);

	/* 删除招标信息 */
	@Transactional
	@Modifying
	@Query(value = "delete from tBidInvite where iInviteID in (:iInviteIDs)", nativeQuery = true)
	void deleteChecked(@Param("iInviteIDs") List<Integer> iInviteIDs);

	/**
	 * 个人中心通过登录人获取招标信信息 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tBidInvite where cCreator=?1  and cSpare1=?2 order by dBeginDate desc", nativeQuery = true)
	int countByCreator(String strUserName, int iOrgID);

	/**
	 * 后台招标管理中显示自己公司的招标信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from tBidInvite where (:strInviteName = \"\" or cInviteName like %:strInviteName%) and (:strType = \"\" or iType = :strType) and cSpare1=:iOrgID order by dBeginDate desc", nativeQuery = true)
	int countByOrgID(@Param("iOrgID") int iOrgID, @Param("strInviteName") String strInviteName,
			@Param("strType") String strType);

	/**
	 * 后台招标管理中超级管理员登录显示所有公司的招标信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from tBidInvite where (:strInviteName = \"\" or cInviteName like %:strInviteName%) and (:strType = \"\" or iType = :strType) order by dBeginDate desc", nativeQuery = true)
	int countALL(@Param("strInviteName") String strInviteName, @Param("strType") String strType);

	/**
	 * 函数功能说明： 前台招投标模块登陆人只能查看所属公司区域市相同的招标 获取分页
	 *
	 * @param 参数说明 tBidInvite
	 * @return 返回值说明 jsp页面
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.11.19
	 */
	@Query(value = "select count(*)  from tBidInvite where (:strInviteName = \"\" or cInviteName like %:strInviteName%) and (:strType = \"\" or iType = :strType) and (:strBudget = \"\" or fBudget = :strBudget) and left(iZoneID,4) =left(:iZoneID,4) and iState=3 order by dBeginDate desc", nativeQuery = true)
	int countByCityID(@Param("strInviteName") String strInviteName, @Param("strType") String strType,
			@Param("strBudget") String strBudget, @Param("iZoneID") int iZoneID);

	/**
	 * 后台招标管理中显示要审核的招标信息 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.11.19 修改时间2021.03.17
	 */
	@Query(value = "select count(*)  from tBidInvite where  iState=2  order by dBeginDate desc", nativeQuery = true)
	int countReview();
}
